<?php
class Sale extends AppModel {
    var $name = 'Sale';

    public function listing ($params, $m) {
        $page = $params['page'];
        $cond = 's.`order_status` IS NOT NULL ';

        if ($params['query'] && $params['qtype']) {
            $cond .= 'AND s.`'.$params['qtype'].'` LIKE "%'.$params['query'].'%" ';
        }

        $sql = 'SELECT COUNT(*) AS total FROM `sales` AS s ' .
               'WHERE '.$cond;
        $ttl = $this->query($sql);

        $total = (count($ttl) == 0) ? 0 : $ttl[0][0]['total'];
        if ($total == 0) {
            return 0;
        }

        $rpp   = isset($params['rp']) ? $params['rp'] : 10;
        $start = (($page-1) * $rpp);
        $sort  = empty($params['sortname']) ? 'id' : $params['sortname'];
        $ord   = (empty($params['sortorder']) || $params['sortorder'] == 'asc') ? 'ASC' : 'DESC';
        $sql   = 'SELECT s.*, m.`fullname`, s2.`country` FROM `sales` AS s ' .
                 'INNER JOIN `members` AS m ' .
                 'ON s.`member_id`=m.`id` ' .
                 'INNER JOIN `stockists` AS s2 ' .
                 'ON s.`stockist_id`=s2.`id` ' .
                 'WHERE '.$cond .
                 'ORDER BY s.`'.$sort.'` '.$ord.' ' .
                 'LIMIT '.$start.', '.$rpp;
        $data = $this->query($sql);
        $rec  = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $rs) {
            $id = $rs['s']['id'];

            $actions  = '<div title="'.__('Modify',true).'" class="editRecord" onclick="editRecord('.$id.');"></div>' .
                        '<div title="'.__('Report',true).'" class="report"></div><div class="clear" style="height:0; padding:0;"></div>';
            $pt       = $rs['s']['payment_type'];
            $ps       = $rs['s']['payment_status'];
            $os       = $rs['s']['order_status'];
            $dm       = $rs['s']['delivery_method'];
            $saleDate = $this->serverToClientTime($rs['s']['sales_date'], 'd/m/Y H:i a');
            $created  = $this->serverToClientTime($rs['s']['created'], 'd/m/Y H:i a');
            $modified = $this->serverToClientTime($rs['s']['modified'], 'd/m/Y H:i a');

            //$paymentType = '<div id="pt'.$id.'" class="pt" onclick="changeType('.$id.');">'.$m['paymentType'][$pt].'</div>';

            $cell = array(
                $rs['s']['id'], $rs['m']['fullname'], $rs['s2']['country'], $m['paymentType'][$pt], $saleDate, $m['paymentStatus'][$ps], $m['orderStatus'][$os],
                $m['deliverMethod'][$dm], $rs['s']['member_id'], $rs['s']['amount'], $rs['s']['amount_converted'], $rs['s']['total_pv'], $rs['s']['stockist_id'],
                $created, $modified, $actions
            );

            $rec[] = array(
                'id'   => $id,
                'cell' => $cell
            );
        }

        return array(
            'page'  => $page,
            'total' => $total,
            'rows'  => $rec
        );
    } // listing

    public function saleList ($params, $m, $id) {
        $page = $params['page'];
        $cond = 's.`member_id`='.$id.' AND s.`order_status` IS NOT NULL ';

        if ($params['query'] && $params['qtype']) {
            $cond .= 'AND s.`'.$params['qtype'].'` LIKE "%'.$params['query'].'%" ';
        }

        $sql = 'SELECT COUNT(*) AS total FROM `sales` AS s ' .
               'WHERE '.$cond;
        $ttl = $this->query($sql);

        $total = (count($ttl) == 0) ? 0 : $ttl[0][0]['total'];
        if ($total == 0) {
            return 0;
        }

        $rpp   = isset($params['rp']) ? $params['rp'] : 10;
        $start = (($page-1) * $rpp);
        $sort  = empty($params['sortname']) ? 'id' : $params['sortname'];
        $ord   = (empty($params['sortorder']) || $params['sortorder'] == 'asc') ? 'ASC' : 'DESC';
        $sql   = 'SELECT s.* FROM `sales` AS s ' .
                 //'INNER JOIN `stockists` AS s2 ' .
                 //'ON s.`stockist_id`=s2.`id` ' .
                 'WHERE '.$cond .
                 'ORDER BY s.`'.$sort.'` '.$ord.' ' .
                 'LIMIT '.$start.', '.$rpp;
        $data = $this->query($sql);
        $rec  = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $rs) {
            $id = $rs['s']['id'];

            $actions  = '<div title="'.__('Details',true).'" class="report" style="margin-left:5px;"></div><div class="clear" style="height:0; padding:0;"></div>';
            $pt       = $rs['s']['payment_type'];
            $ps       = $rs['s']['payment_status'];
            $os       = $rs['s']['order_status'];
            $dm       = $rs['s']['delivery_method'];
            $saleDate = $this->serverToClientTime($rs['s']['sales_date'], 'd/m/Y H:i a');

            //$paymentType = '<div id="pt'.$id.'" class="pt" onclick="changeType('.$id.');">'.$m['paymentType'][$pt].'</div>';

            $pt = ($pt) ? $m['paymentType'][$pt] : '-';
            $ps = ($ps) ? $m['paymentStatus'][$ps] : '-';
            $os = ($os) ? $m['orderStatus'][$os] : '-';
            $dm = ($dm) ? $m['deliverMethod'][$dm] : '-';
            $ac = ($rs['s']['amount_converted']) ? $rs['s']['amount_converted'] : number_format($rs['s']['amount'] * 4, 2);

            $cell = array($rs['s']['id'], $pt, $saleDate, $ps, $os, $dm, $rs['s']['amount'], $ac, $rs['s']['total_pv'], $actions);

            $rec[] = array(
                'id'   => $id,
                'cell' => $cell
            );
        }

        return array(
            'page'  => $page,
            'total' => $total,
            'rows'  => $rec
        );
    } // saleList

    public function userRank ($uid, $code='') {
        $cond = ($code) ? 's.`member_code`="'.$code.'"' : 's.`member_id`='.$uid;
        $sql  = 'SELECT SUM(`total_pv`) AS `bv` FROM `sales` AS s ' .
                'WHERE '.$cond;
        $data = $this->query($sql);

        if (!$data[0][0]['bv']) {
            return false;
        }

        $type = ($data[0][0]['bv'] < 280) ? 1 : 10;

        return $type;
    } // userRank

    public function upgradeOrderBv ($uid, $date) {
        $sql  = 'SELECT SUM(`total_pv`) AS `total` FROM `sales` AS s ' .
                'WHERE s.`member_id`='.$uid.' AND s.`payment_status`="X" AND s.`confirm_date` LIKE "'.$date.'%"';
        $data = $this->query($sql);

        if (!$data[0][0]['total']) {
            return false;
        }

        return $data[0][0]['total'];
    } // upgradeOrderBv

    public function monthlySalesReport ($filters) {
        if ($filters['from'] == $filters['to']) {
            $cond = ' AND o.`sales_date` LIKE "'.$filters['from'].'%"';
        } else {
            $cond = ' AND o.`sales_date` LIKE "'.$filters['from'].'%" OR o.`sales_date` LIKE "'.$filters['to'].'%"';
        }

        $sql  = 'SELECT o.`sales_date`, o.`amount`, s.`name`, s.`country` FROM `sales` AS o ' .
                'INNER JOIN `stockists` AS s ON o.`stockist_id`=s.`id` ' .
                'WHERE o.`payment_status`!="D"'.$cond.' ' .
                'ORDER BY o.`sales_date` ASC';
        $data = $this->query($sql);
        $resp = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $i=>$r) {
            $month   = substr($r['o']['sales_date'], 0 ,7);
            $amount  = $r['o']['amount'];
            $name    = $r['s']['name'];
            $country = $r['s']['country'];

            if (isset($resp[$month][$country][$name])) {
                $resp[$month][$country][$name] += $amount;
            } else {
                $resp[$month][$country][$name] = $amount;
            }
        }

        return $resp;
    } // monthlySalesReport

    public function orderReport ($filters) {
        $cond = $this->_generateCondition($filters);
        $sql  = 'SELECT o.`id`, o.`sales_date`, o.`amount`, o.`total_pv`, m.`id`, m.`fullname`, s.`id`, s.`name`, s.`country` FROM `sales` AS o ' .
                'INNER JOIN `members` AS m ' .
                'ON o.`member_id`=m.`id` ' .
                'INNER JOIN `stockists` AS s ' .
                'ON o.`stockist_id`=s.`id` ' .
                'WHERE o.`payment_status`!="D"'.$cond.' ' .
                'ORDER BY o.`sales_date` ASC';
        $data = $this->query($sql);
        $resp = array(
            'totalBv'     => 0,
            'totalAmount' => 0
        );

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $i=>$r) {
            $resp['list'][$i] = array(
                'orderNo'    => $r['o']['id'],
                'salesDate'  => $this->serverToClientTime($r['o']['sales_date'], 'd/m/Y H:i a'),
                'memberId'   => $r['m']['id'],
                'memberName' => $r['m']['fullname'],
                'country'    => $r['s']['country'],
                'stockistId' => $r['s']['id'],
                'stockist'   => $r['s']['name'],
                'bv'         => $r['o']['total_pv'],
                'amount'     => $r['o']['amount']
            );

            $resp['totalBv']     += $r['o']['total_pv'];
            $resp['totalAmount'] += $r['o']['amount'];
        }

        return $resp;
    } // orderReport

    public function statisticReport ($filters) {
        $cond = $this->_generateCondition($filters);
        $sql  = 'SELECT i.`product_id`, i.`product_code`, i.`quantity`, i.`bv`, i.`price` FROM `sale_items` AS i ' .
                'INNER JOIN `sales` as o ' .
                'ON i.`sale_id`=o.`id` ' .
                'WHERE o.`payment_status`="X"'.$cond.' AND i.`bv`!=0 ' .
                'ORDER BY i.`product_code` ASC';
        $data = $this->query($sql);
        $resp = array(
            'totalBv'      => 0,
            'totalAmount1' => 0,
            'totalAmount2' => 0
        );
        $curr = '';
        $i    = -1;

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $r) {
            if ($curr != $r['i']['product_code']) {
                $curr = $r['i']['product_code'];
                $i++;

                $productName = $this->_getProductName($r['i']['product_id']);
                $resp['list'][$i] = array(
                    'code'    => $r['i']['product_code'],
                    'name'    => $productName,
                    'qty'     => 0,
                    'bv'      => 0,
                    'amount1' => 0,
                    'amount2' => 0
                );
            }

            $resp['list'][$i]['qty']     += $r['i']['quantity'];
            $resp['list'][$i]['bv']      += $r['i']['bv'] * $r['i']['quantity'];
            $resp['list'][$i]['amount1'] += $r['i']['price'] * $r['i']['quantity'];
            $resp['list'][$i]['amount2'] += $r['i']['price'] * $r['i']['quantity'];

            $resp['totalBv']      += $r['i']['bv'];
            $resp['totalAmount1'] += $r['i']['price'];
            $resp['totalAmount2'] += $r['i']['price'];
        }

        return $resp;
    } // statisticReport

    public function inventorySalesReport ($filters) {
        $cond = $this->_generateCondition($filters);
        $sql  = 'SELECT i.`product_id`, i.`product_code`, i.`quantity`, i.`cost` FROM `sale_inventories` AS i ' .
                'INNER JOIN `sales` as o ' .
                'ON i.`sale_id`=o.`id` ' .
                'WHERE o.`payment_status`="X"'.$cond.' ' .
                'ORDER BY i.`product_code` ASC';
        $data = $this->query($sql);
        $resp = array(
            'totalQuantity' => 0,
            'totalAmount'   => 0
        );
        $curr = '';
        $i    = -1;

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $r) {
            if ($curr != $r['i']['product_code']) {
                $curr = $r['i']['product_code'];
                $i++;

                $productName = $this->_getProductName($r['i']['product_id']);
                $resp['list'][$i] = array(
                    'code'  => $r['i']['product_code'],
                    'name'  => $productName,
                    'qty'   => 0,
                    'unit'  => 0,
                    'total' => 0
                );
            }

            $resp['list'][$i]['qty']   += $r['i']['quantity'];
            $resp['list'][$i]['unit']  += $r['i']['cost'];
            $resp['list'][$i]['total'] += $r['i']['cost'] * $r['i']['quantity'];

            $resp['totalQuantity'] += $r['i']['quantity'];
            $resp['totalAmount']   += $resp['list'][$i]['total'];
        }

        return $resp;
    } // inventorySalesReport

    public function topBvPurchaseReport ($filters) {
        $cond = $this->_generateCondition($filters);
        $page = $filters['page'];
        $rpp  = 10;
        $limit = ($page-1) * $rpp;

        $sql  = 'SELECT COUNT(DISTINCT o.`member_id`) AS `pages` FROM `sales` AS o ' .
                'INNER JOIN `members` AS m ' .
                'ON o.`member_id`=m.`id` ' .
                'INNER JOIN `stockists` AS s ' .
                'ON o.`stockist_id`=s.`id` ' .
                'WHERE o.`payment_status`!="D"'.$cond;
        $data = $this->query($sql);

        if (count($data) == 0) {
            return 0;
        }
        $pages = $data[0][0]['pages'];

        if ($page == 1) {
            $prev = 0;
            $next = ($pages > 1) ? 2 : 0;
        } else if ($page == $pages) {
            $prev = $page - 1;
            $next = 0;
        } else {
            $prev = $page - 1;
            $next = $page + 1;
        }

        $sql  = 'SELECT SUM(o.`total_pv`) AS `totalBv`, m.`id`, m.`join_date`, m.`fullname`, m.`country`, m.`telephone`, m.`mobile` FROM `sales` AS o ' .
                'INNER JOIN `members` AS m ' .
                'ON o.`member_id`=m.`id` ' .
                'INNER JOIN `stockists` AS s ' .
                'ON o.`stockist_id`=s.`id` ' .
                'WHERE o.`payment_status`!="D"'.$cond.' ' .
                'GROUP BY o.`member_id` ' .
                'ORDER BY `totalBv` DESC, m.`fullname` ASC LIMIT '.$limit.', '.$rpp;
        $data = $this->query($sql);
        $resp = array(
            'totalBv'     => 0,
            'totalAmount' => 0,
            'pages'       => $pages,
            'page'        => $page,
            'prev'        => $prev,
            'next'        => $next
        );

        if (count($data) == 0) {
            return 0;
        }

        if (!empty($filters['date']) && strpos($filters['date'], ' - ')) {
            list($start, $end) = split(' - ', $filters['date']);

            list($day, $month, $year) = split('[/.-]', $start);
            $start = $year.'-'.$month.'-'.$day;
            list($day, $month, $year) = split('[/.-]', $end);
            $end = $year.'-'.$month.'-'.$day;
        } else if (!empty($filters['date'])) {
            list($day, $month, $year) = split('[/.-]', $filters['date']);
            $date = $year.'-'.$month.'-'.$day;
        }

        foreach ($data as $i=>$r) {
            $resp['list'][$i] = array(
                'userId'   => $r['m']['id'],
                'fullname' => $r['m']['fullname'],
                'country'  => $r['m']['country'],
                'totalBv'  => $r[0]['totalBv'],
                'first'    => 0,
                'upgrade'  => 0,
                're-order' => $r[0]['totalBv'],
                'phone'    => $r['m']['telephone'],
                'mobile'   => $r['m']['mobile']
            );

            $join = substr($r['m']['join_date'], 0, 10);
            $moth = substr($r['m']['join_date'], 0, 7);
            $calc = false;

            if (isset($date) && $join == $date) { // single date range
                $calc = true;
            } else if (isset($start) && isset($end)) { // within 2 dates
                if ($start <= $join && $join <= $end) {
                    $calc = true;
                }
            }
            if ($calc) {
                $sql  = 'SELECT `total_pv` FROM `sales` AS s ' .
                        'WHERE `member_id`='.$r['m']['id'].' AND `payment_status`="X" AND `confirm_date` LIKE "'.$moth.'%" ' .
                        'ORDER BY `confirm_date` ASC';
                $sale = $this->query($sql);
                $first = $upgrade = 0;

                foreach ($sale as $x=>$s) {
                    if ($x == 0) {
                        $first = $s['s']['total_pv'];
                    } else {
                        $upgrade += $s['s']['total_pv'];
                    }
                }
                $reorder = $r[0]['totalBv'] - ($first + $upgrade);

                $resp['list'][$i]['first']    = $first;
                $resp['list'][$i]['upgrade']  = $upgrade;
                $resp['list'][$i]['re-order'] = $reorder;
            }
        }

        return $resp;
    } // topBvPurchaseReport

    public function firstGenSponsoredBvReport ($filters) {
        $cond = $this->_generateCondition($filters);
        $sql  = 'SELECT SUM(o.`total_pv`) AS `totalBv`, m.`member_id`, u.`fullname`, u.`country`, u.`telephone`, u.`mobile`, u.`mobile` FROM `sales` AS o ' .
                'INNER JOIN `members` AS m ON o.`member_id`=m.`id` ' .
                'INNER JOIN `members` AS u ON m.`member_id`=u.`id` ' .
                'INNER JOIN `stockists` AS s ON o.`stockist_id`=s.`id` ' .
                'WHERE o.`payment_status`!="D" AND m.`member_id` IS NOT NULL '.$cond.' ' .
                'GROUP BY m.`member_id` ' .
                'ORDER BY `totalBv` DESC, u.`fullname` ASC';
        $data = $this->query($sql);
        $resp = array(
            'totalBv'     => 0,
            'totalAmount' => 0
        );

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $i=>$r) {
            $resp['list'][$i] = array(
                'userId'   => $r['m']['member_id'],
                'fullname' => $r['u']['fullname'],
                'country'  => $r['u']['country'],
                'totalBv'  => $r[0]['totalBv'],
                'phone'    => $r['u']['telephone'],
                'mobile'   => $r['u']['mobile']
            );
        }

        return $resp;
    } // firstGenSponsoredBvReport

    public function topSalesReport ($filters) {
        $cond = $this->_generateCondition($filters);
        $sql  = 'SELECT o.`total_pv`, m.`id`, m.`fullname`, m.`country`, m.`telephone`, m.`mobile` FROM `sales` AS o ' .
                'INNER JOIN `members` AS m ' .
                'ON o.`member_id`=m.`id` ' .
                'INNER JOIN `stockists` AS s ' .
                'ON o.`stockist_id`=s.`id` ' .
                'WHERE o.`payment_status`!="D" AND o.`total_pv`>0'.$cond.' ' .
                'ORDER BY m.`member_id` ASC';
        $data = $this->query($sql);
        $sale = $order = array();
        $temp = '';
        $i    = -1;

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $r) {
            if ($r['m']['id'] != $temp) {
                $i   += 1;
                $temp = $r['m']['id'];

                $order[$i] = 0;
                $sale[$i]  = array(
                    'userId'   => $r['m']['id'],
                    'fullname' => $r['m']['fullname'],
                    'country'  => $r['m']['country'],
                    'phone'    => $r['m']['telephone'],
                    'mobile'   => $r['m']['mobile'],
                    'orders'   => 0,
                    'totalBv'  => 0
                );
            }

            $order[$i]           += $r['o']['total_pv'];
            $sale[$i]['orders']  += 1;
            $sale[$i]['totalBv'] += $r['o']['total_pv'];
        }

        array_multisort($order, SORT_DESC, $sale);
        $resp['list'] = $sale;

        return $resp;
    } // topSalesReport


    private function _generateCondition ($filters) {
        $cond = '';

        if (!empty($filters['date']) && strpos($filters['date'], ' - ')) {
            list($start, $end) = split(' - ', $filters['date']);

            list($day, $month, $year) = split('[/.-]', $start);
            $start = $year.'-'.$month.'-'.$day.' 00:00:00';
            list($day, $month, $year) = split('[/.-]', $end);
            $end = $year.'-'.$month.'-'.$day.' 23:59:59';

            $cond = ' AND o.`sales_date` > "'.$start.'" AND o.`sales_date` < "'.$end.'"';
        } else if (!empty($filters['date'])) {
            list($day, $month, $year) = split('[/.-]', $filters['date']);
            $cond = ' AND o.`sales_date` LIKE "'.$year.'-'.$month.'-'.$day.'%"';
        }
        if (!empty($filters['stockist'])) {
            $cond .= ' AND o.`stockist_id`='.$filters['stockist'];
        }

        return $cond;
    } // _generateCondition

    private function _getProductName ($id) {
        $sql  = 'SELECT `name` FROM `products` AS p WHERE `id`='.$id;
        $data = $this->query($sql);

        if (count($data) == 0) {
            return '';
        }

        return $data[0]['p']['name'];
    } // _productName
} // sale
?>